<%@ page import="jdbc.H2ConnTest1" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %><%--
  Created by IntelliJ IDEA.
  User: 徐鑫磊
  Date: 2024/4/15
  Time: 15:38
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form action="test.jsp" method="post">
    学生成绩查询<br>
    输入学生学号查询：<input type="text" id="sno"><br>
    输入成绩分数段查询：<input type="text" id="min">,<input type="text" id="max"><br>
    输入班级名查询：<input type="text" id="clazz"><br>
    <button type="submit">提交</button>
</form>

<%


     String JDBC_URL = "jdbc:h2:d:/temp/teaching";
    //连接数据库时使用的用户名
     String USER = "sa";
    //连接数据库时使用的密码
    String PASSWORD = "";
    //连接H2数据库时使用的驱动类，org.h2.Driver这个类是由H2数据库自己提供的，在H2数据库的jar包中可以找到
     String DRIVER_CLASS="org.h2.Driver";
     Connection con = null;
    //第一个java代码段查询数据
    try{
        Class.forName(DRIVER_CLASS);//加载驱动
        con = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        return;
    }

    String sno = (String) request.getAttribute("sno");
    String min = (String) request.getAttribute("min");
    String max = (String) request.getAttribute("max");
    String clazz = (String) request.getAttribute("clazz");



    if (sno != null && !sno.isEmpty()) {
        // 根据学号查询
        String sql = "SELECT * FROM student WHERE no = ?";
        try (PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, sno);
            ResultSet rs = stmt.executeQuery();
            // 处理查询结果
        }
    } else if (min != null && !min.isEmpty() && max != null && !max.isEmpty()) {
        // 根据分数段查询

        String sql = "SELECT * FROM student WHERE score BETWEEN ? AND ?";
        try (PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, min);
            stmt.setString(2, max);
            ResultSet rs = stmt.executeQuery();
            // 处理查询结果
        }
    } else if (clazz != null && !clazz.isEmpty()) {
        // 根据班级名查询
        String sql = "SELECT * FROM student WHERE clazz = ?";
        try (PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setString(1, clazz);
            ResultSet rs = stmt.executeQuery();
            // 处理查询结果
        }
    } else {
        // 显示查询失败信息
    }

%>
</body>
</html>
